﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Linq;
using System.Text;

using DMS.Business.Utility;
using DMS.Business.Modules.Organization;
using DMS.Base;
using DMS.Business.Modules.SupplierManagement;
using DMS.Business.BaseClass;
using DMS.Data;

namespace DMS.Business.Utility
{
    public class SupplierHelper : BaseService
    {
        #region 供应商信息相关方法

        #region 供应商表相关SQL句子
        private const string SQL_SPM_Suppliers = @"SELECT {0} [SupplierID]
                                                          ,[Name]
                                                          ,[ShortName]
                                                          ,[Contact]
                                                          ,[Phone]
                                                          ,ss.[Mobile]
                                                          ,[FAX]
                                                          ,ss.[Email]
                                                          ,[Address]
                                                          ,[IsDeleted]
                                                      FROM [dbo].[SPM_Suppliers] ss 
                                                        @joinClause@";
        private const string SQL_SPM_Suppliers_Count = @"SELECT COUNT(*)
                                                         FROM [dbo].[SPM_Suppliers] 
                                                            @joinClause@";
        private const string SQL_SPM_Suppliers_Order = @"ORDER BY [Name]";
        private const string SQL_SPM_Suppliers_Add = @"INSERT INTO [dbo].[SPM_Suppliers]
                                                                   ([SupplierID]
                                                                   ,[Name]
                                                                   ,[ShortName]
                                                                   ,[Contact]
                                                                   ,[Phone]
                                                                   ,[Mobile]
                                                                   ,[FAX]
                                                                   ,[Email]
                                                                   ,[Address]
                                                                   ,[IsDeleted])
                                                             select @SupplierID
                                                                   ,@Name
                                                                   ,@ShortName
                                                                   ,@Contact
                                                                   ,@Phone
                                                                   ,@Mobile
                                                                   ,@FAX
                                                                   ,@Email
                                                                   ,@Address
                                                                   ,@IsDeleted
                                                             from org_users ou where ou.userid=@userID";
        private const string SQL_SPM_Suppliers_Update = @"UPDATE [dbo].[SPM_Suppliers]
                                                           SET [Name] = @Name
                                                              ,[ShortName] = @ShortName
                                                              ,[Contact] = @Contact
                                                              ,[Phone] = @Phone
                                                              ,[Mobile] = @Mobile
                                                              ,[FAX] = @FAX
                                                              ,[Email] = @Email
                                                              ,[Address] = @Address
                                                              ,[IsDeleted] = @IsDeleted
                                                         WHERE [SupplierID] = @SupplierID";
        private const string SQL_SPM_Suppliers_Delete = @"update [dbo].[SPM_Suppliers]
                                                          set IsDeleted = 1
                                                          WHERE [SupplierID] = @SupplierID";
        #endregion

        public static Supplier GetSupplierByID(string supplierID)
        {
            List<DbParameter> dbParams = new List<DbParameter>();

            DbParameter p_supplierID = DBHelper.CreateParamer();
            p_supplierID.ParameterName = "SupplierID";
            p_supplierID.Value = supplierID;
            dbParams.Add(p_supplierID);

            int total = 0;
            string condition = "AND [SupplierID] = @SupplierID";
            List<Supplier> suppliers = GetSuppliers(1, 0, out total, condition, dbParams);

            return total > 0 ? suppliers[0] : null;
        }
        public static Supplier GetSupplierByName(string name)
        {
            List<DbParameter> dbParams = new List<DbParameter>();

            DbParameter p_supplierName = DBHelper.CreateParamer();
            p_supplierName.ParameterName = "Name";
            p_supplierName.Value = name;
            dbParams.Add(p_supplierName);

            int total = 0;
            string condition = "AND [Name] = @Name";
            List<Supplier> suppliers = GetSuppliers(1, 0, out total, condition, dbParams);

            return total > 0 ? suppliers[0] : null;
        }

        public static List<Supplier> GetSuppliers()
        {
            int total = 0;
            return GetSuppliers(0, 1, out total, "", null);

            //var q = from supplier in DB.SPM_Suppliers
            //        where (supplier.IsDeleted == false)
            //        select supplier;

            //List<SPM_Suppliers> list = q.ToList();
        }



        private static List<Supplier> GetSuppliers(int limite, int page, out int total, string condition, List<DbParameter> dbParams)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;

            string joinClause = string.Empty;
            string andClause = string.Empty;
            //string userid = SystemContext.Current.LoginUser == null ? "" : SystemContext.Current.LoginUser.ID.ToString();
            //if (!string.IsNullOrEmpty(userid))
            //{
                //joinClause = "join org_users ou on SupplierOwnerDeptID = dbo.f_GetTypeDeptID(ou.deptid)";
                //andClause = " and ou.userid='" + userid + "' ";
            //}

            string whereClause = string.Format("where IsDeleted=0 {0}", condition);

            string countSql = string.Format("{0} {1} @andClause@", SQL_SPM_Suppliers_Count, whereClause);

            countSql = countSql.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);

            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause =
                        string.Format(
                            @"SELECT TOP {0} [SupplierID] FROM [dbo].[SPM_Suppliers] @joinClause@ {1} @andClause@ {2}",
                            pageCount.ToString(), whereClause, SQL_SPM_Suppliers_Order);
                    subClause = subClause.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);
                    whereClause += string.Format("AND [SupplierID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_SPM_Suppliers, topClause);
            string sql = string.Format("{0} {1} @andClause@ {2}", mainClause, whereClause, SQL_SPM_Suppliers_Order);

            sql = sql.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);

            DbCommand command = db.GetSqlStringCommand(sql);
            if (dbParams != null && dbParams.Count > 0)
            {
                command.Parameters.AddRange(dbParams.ToArray());
            }

            DataSet ds = db.ExecuteDataSet(command);
            List<Supplier> suppliers = new List<Supplier>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, dbParams);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Supplier supplier = new Supplier(dr["SupplierID"].ToString());
                    FillSupplierInfo(dr, supplier);

                    suppliers.Add(supplier);
                }
            }

            return suppliers;
        }

        public static void AddSupplier(Supplier supplier)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_Suppliers_Add);
            FillSupplierParams(supplier, cmd, null);

            db.ExecuteNonQuery(cmd);
        }
        public static void UpdateSupplier(Supplier supplier)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_Suppliers_Update);
            FillSupplierParams(supplier, cmd, null);

            db.ExecuteNonQuery(cmd);
        }
        public static void DeleteSupplier(Supplier supplier)
        {
            foreach (SupplierAccount account in supplier.Accounts)
            {
                account.Delete();
            }
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_Suppliers_Delete);
            FillSupplierParams(supplier, cmd, new string[] { "SupplierID" });

            db.ExecuteNonQuery(cmd);
        }

        private static void FillSupplierParams(Supplier supplier, DbCommand cmd, string[] paramsList)
        {
            if (supplier == null || cmd == null)
            {
                return;
            }
            string paramsStr = string.Empty;
            if (paramsList != null && paramsList.Length > 0)
            {
                paramsStr = string.Format("{0}#;", string.Join("#;", paramsList));
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("SupplierID#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_SupplierID = cmd.CreateParameter();
                p_SupplierID.ParameterName = "SupplierID";
                p_SupplierID.Value = supplier.ID;
                cmd.Parameters.Add(p_SupplierID);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Name#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Name = cmd.CreateParameter();
                p_Name.ParameterName = "Name";
                p_Name.Value = supplier.Name;
                cmd.Parameters.Add(p_Name);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("ShortName#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_ShortName = cmd.CreateParameter();
                p_ShortName.ParameterName = "ShortName";
                p_ShortName.Value = supplier.ShortName;
                cmd.Parameters.Add(p_ShortName);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Contact#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Contact = cmd.CreateParameter();
                p_Contact.ParameterName = "Contact";
                p_Contact.Value = supplier.Contact;
                cmd.Parameters.Add(p_Contact);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Phone#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Phone = cmd.CreateParameter();
                p_Phone.ParameterName = "Phone";
                p_Phone.Value = supplier.Phone;
                cmd.Parameters.Add(p_Phone);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Mobile#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Mobile = cmd.CreateParameter();
                p_Mobile.ParameterName = "Mobile";
                p_Mobile.Value = supplier.Mobile;
                cmd.Parameters.Add(p_Mobile);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("FAX#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_FAX = cmd.CreateParameter();
                p_FAX.ParameterName = "FAX";
                p_FAX.Value = supplier.Fax;
                cmd.Parameters.Add(p_FAX);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Email#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Email = cmd.CreateParameter();
                p_Email.ParameterName = "Email";
                p_Email.Value = supplier.Email;
                cmd.Parameters.Add(p_Email);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Address#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Address = cmd.CreateParameter();
                p_Address.ParameterName = "Address";
                p_Address.Value = supplier.Address;
                cmd.Parameters.Add(p_Address);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("IsDeleted#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_IsDeleted = cmd.CreateParameter();
                p_IsDeleted.ParameterName = "IsDeleted";
                p_IsDeleted.Value = (bool)supplier.IsDeleted;
                cmd.Parameters.Add(p_IsDeleted);
            }
            
            DbParameter userID = cmd.CreateParameter();
            userID.ParameterName = "userID";
            userID.Value = SystemContext.Current.LoginUser.ID;
            cmd.Parameters.Add(userID);

        }
        private static void FillSupplierInfo(DataRow dr, Supplier supplier)
        {
            if (dr == null)
            {
                throw new ArgumentNullException("dr", "装载供应商信息时，传入的数据行对象不能为空。");
            }
            if (supplier == null)
            {
                supplier = new Supplier(dr["SupplierID"].ToString());
            }

            supplier.Name = dr["Name"].ToString();
            supplier.ShortName = dr["ShortName"].ToString();
            supplier.Contact = dr["Contact"].ToString();
            supplier.Phone = dr["Phone"].ToString();
            supplier.Mobile = dr["Mobile"].ToString();
            supplier.Email = dr["Email"].ToString();
            supplier.Fax = dr["Fax"].ToString();
            supplier.Address = dr["Address"].ToString();

            if (dr["IsDeleted"] != null && dr["IsDeleted"].ToString() != string.Empty)
            {
                supplier.IsDeleted = bool.Parse(dr["IsDeleted"].ToString());
            }

        }
        #endregion

        #region 供应商帐号相关信息

        #region 供应商帐号表相关SQL句子
        private const string SQL_SPM_SupplierAccounts = @"SELECT {0} [AccountID]
                                                                  ,[SupplierID]
                                                                  ,[Supplier]
                                                                  ,[AccountName]
                                                                  ,[LoginID]
                                                                  ,[StaticPWD]
                                                                  ,[SPWDModifyTime]
                                                                  ,[DynamicPWD]
                                                                  ,[DPWDCreateTime]
                                                                  ,[ValidDate]
                                                                  ,[IsDefault]
                                                                  ,[Mobile]
                                                                  ,[Email]
                                                                  ,[ProjectTypeID]
                                                                  ,[ProjectTypeName]
                                                                  ,[Position]
                                                                  ,[IsManager]
                                                                  ,[IsDeleted]
                                                                 FROM [dbo].[SPM_SupplierAccounts] ";
        private const string SQL_SPM_SupplierAccounts_Count = @"SELECT COUNT(*)
                                                                FROM [dbo].[SPM_SupplierAccounts]";
        private const string SQL_SPM_SupplierAccounts_Order = @"ORDER BY [SupplierID], [LoginID]";
        private const string SQL_SPM_SupplierAccounts_Add = @"INSERT INTO [dbo].[SPM_SupplierAccounts]
                                                                           ([AccountID]
                                                                           ,[SupplierID]
                                                                           ,[Supplier]
                                                                           ,[AccountName]
                                                                           ,[LoginID]
                                                                           ,[StaticPWD]
                                                                           ,[SPWDModifyTime]
                                                                           ,[ValidDate]
                                                                           ,[IsDefault]
                                                                           ,[Mobile]
                                                                           ,[Email]
                                                                           ,[ProjectTypeID]
                                                                           ,[ProjectTypeName]
                                                                           ,[Position]
                                                                           ,[IsManager]
                                                                           ,[IsDeleted])
                                                                     VALUES
                                                                           (@AccountID
                                                                           ,@SupplierID
                                                                           ,@Supplier
                                                                           ,@AccountName
                                                                           ,@LoginID
                                                                           ,@StaticPWD
                                                                           ,@SPWDModifyTime
                                                                           ,@ValidDate
                                                                           ,@IsDefault
                                                                           ,@Mobile
                                                                           ,@Email
                                                                           ,@ProjectTypeID
                                                                           ,@ProjectTypeName
                                                                           ,@Position
                                                                           ,@IsManager
                                                                           ,@IsDeleted)";
        private const string SQL_SPM_SupplierAccounts_Update = @"UPDATE [dbo].[SPM_SupplierAccounts]
                                                                   SET [SupplierID] = @SupplierID
                                                                      ,[Supplier] = @Supplier
                                                                      ,[AccountName] = @AccountName
                                                                      ,[LoginID] = @LoginID
                                                                      ,[StaticPWD] = @StaticPWD
                                                                      ,[SPWDModifyTime] = @SPWDModifyTime
                                                                      ,[ValidDate] = @ValidDate
                                                                      ,[IsDefault] = @IsDefault
                                                                      ,[Mobile] = @Mobile
                                                                      ,[Email] = @Email
                                                                      ,[ProjectTypeID] = @ProjectTypeID
                                                                      ,[ProjectTypeName] = @ProjectTypeName
                                                                      ,[Position] = @Position
                                                                      ,[IsManager] = @IsManager
                                                                      ,[IsDeleted] = @IsDeleted
                                                                 WHERE [AccountID] = @AccountID";
        private const string SQL_SPM_SupplierAccounts_Delete = @"update [dbo].[SPM_SupplierAccounts]
                                                                 set IsDeleted = 1
                                                                 WHERE [AccountID] = @AccountID";
        #endregion

        public static SupplierAccount GetContextAccount()
        {
            SupplierAccount contextAccount = null;

            HttpContext context = HttpContext.Current;
            if (context !=null && context.User.Identity.IsAuthenticated)
            {
                string accountLoginID = context.User.Identity.Name;
                //if (HttpContext.Current.Cache["LoginUser" + accountLoginID] == null)
                //{
                //    contextAccount = SupplierHelper.GetAccountByLoginID(accountLoginID);
                //    HttpContext.Current.Cache["LoginUser" + accountLoginID] = contextAccount;
                //}
                //else
                //{
                //    contextAccount = HttpContext.Current.Cache["LoginUser" + accountLoginID] as SupplierAccount;
                //}
                contextAccount = SupplierHelper.GetAccountByLoginID(accountLoginID);
            }

            return contextAccount;
        }

        public static SupplierAccount GetAccountByID(string accountID)
        {
            List<DbParameter> dbParams = new List<DbParameter>();

            DbParameter p_AccountID = DBHelper.CreateParamer();
            p_AccountID.ParameterName = "AccountID";
            p_AccountID.Value = accountID;
            dbParams.Add(p_AccountID);

            int total = 0;
            string condition = "AND [AccountID] = @AccountID";
            List<SupplierAccount> accounts = GetAccounts(1, 0, out total, condition, dbParams);

            return total > 0 ? accounts[0] : null;
        }
        public static SupplierAccount GetAccountByLoginID(string loginID)
        {
            List<DbParameter> dbParams = new List<DbParameter>();

            DbParameter p_LoginID = DBHelper.CreateParamer();
            p_LoginID.ParameterName = "LoginID";
            p_LoginID.Value = loginID;
            dbParams.Add(p_LoginID);

            int total = 0;
            string condition = "AND [LoginID] = @LoginID";
            List<SupplierAccount> accounts = GetAccounts(1, 0, out total, condition, dbParams);

            return total > 0 ? accounts[0] : null;
        }
        public static List<SupplierAccount> GetAccountBySPID(string supplierID)
        {
            List<DbParameter> dbParams = new List<DbParameter>();

            DbParameter p_SupplierID = DBHelper.CreateParamer();
            p_SupplierID.ParameterName = "SupplierID";
            p_SupplierID.Value = supplierID;
            dbParams.Add(p_SupplierID);

            int total = 0;
            string condition = "AND [SupplierID] = @SupplierID";
            List<SupplierAccount> accounts = GetAccounts(-1, 0, out total, condition, dbParams);

            return accounts;
        }
        
        public static List<SupplierAccount> GetAccounts(int limite, int page, out int total, string condition, List<DbParameter> dbParams)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;
            string whereClause = string.Format("WHERE IsDeleted = 0 {0}", condition);
            string countSql = string.Format("{0} {1}", SQL_SPM_SupplierAccounts_Count, whereClause);
            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause = string.Format(@"SELECT TOP {0} [AccountID]
                                                       FROM [dbo].[SPM_SupplierAccounts] {1}", pageCount.ToString(), whereClause);
                    whereClause += string.Format("AND [AccountID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_SPM_SupplierAccounts, topClause);
            string sql = string.Format("{0} {1} {2}", mainClause, whereClause, SQL_SPM_SupplierAccounts_Order);
            DbCommand command = db.GetSqlStringCommand(sql);
            if (dbParams != null && dbParams.Count > 0)
            {
                command.Parameters.AddRange(dbParams.ToArray());
            }

            DataSet ds = db.ExecuteDataSet(command);
            List<SupplierAccount> accounts = new List<SupplierAccount>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, dbParams);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    SupplierAccount account = new SupplierAccount(dr["AccountID"].ToString());
                    FillSupplierAccountInfo(dr, account);

                    accounts.Add(account);
                }
            }

            return accounts;
        }

        public static void AddSupplierAcount(SupplierAccount account)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_SupplierAccounts_Add);
            FillSupplierAccountParams(account, cmd, null);

            db.ExecuteNonQuery(cmd);
        }
        public static void UpdateSupplierAccount(SupplierAccount account)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_SupplierAccounts_Update);
            FillSupplierAccountParams(account, cmd, null);

            db.ExecuteNonQuery(cmd);
        }
        public static void DeleteSupplierAccount(SupplierAccount account)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_SupplierAccounts_Delete);
            FillSupplierAccountParams(account, cmd, new string[] { "AccountID" });

            db.ExecuteNonQuery(cmd);
        }

        private static void FillSupplierAccountParams(SupplierAccount account, DbCommand cmd, string[] paramsList)
        {
            if (account == null || cmd == null)
            {
                return;
            }
            string paramsStr = string.Empty;
            if (paramsList != null && paramsList.Length > 0)
            {
                paramsStr = string.Format("{0}#;", string.Join("#;", paramsList));
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("AccountID#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_AccountID = cmd.CreateParameter();
                p_AccountID.ParameterName = "AccountID";
                p_AccountID.Value = account.ID;
                cmd.Parameters.Add(p_AccountID);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("SupplierID#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_SupplierID = cmd.CreateParameter();
                p_SupplierID.ParameterName = "SupplierID";
                p_SupplierID.Value = account.SupplierID;
                cmd.Parameters.Add(p_SupplierID);
            }
            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Supplier#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Supplier = cmd.CreateParameter();
                p_Supplier.ParameterName = "Supplier";
                p_Supplier.Value = account.Supplier;
                cmd.Parameters.Add(p_Supplier);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("AccountName#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_AccountName = cmd.CreateParameter();
                p_AccountName.ParameterName = "AccountName";
                p_AccountName.Value = account.Name;
                cmd.Parameters.Add(p_AccountName);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("LoginID#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_LoginID = cmd.CreateParameter();
                p_LoginID.ParameterName = "LoginID";
                p_LoginID.Value = account.LoginID;
                cmd.Parameters.Add(p_LoginID);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Mobile#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Mobile = cmd.CreateParameter();
                p_Mobile.ParameterName = "Mobile";
                p_Mobile.Value = account.Mobile;
                cmd.Parameters.Add(p_Mobile);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Email#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_EMail = cmd.CreateParameter();
                p_EMail.ParameterName = "Email";
                p_EMail.Value = account.Email;
                cmd.Parameters.Add(p_EMail);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("IsDefault#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_IsDefault = cmd.CreateParameter();
                p_IsDefault.ParameterName = "IsDefault";
                p_IsDefault.Value = account.IsDefaultAccount ? 1 : 0;
                cmd.Parameters.Add(p_IsDefault);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("ValidDate#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_ValidDate = cmd.CreateParameter();
                p_ValidDate.ParameterName = "ValidDate";
                if (account.ValidDate == DateTime.MinValue
                    || account.ValidDate == DateTime.MaxValue)
                {
                    p_ValidDate.Value = DBNull.Value;
                }
                else
                {
                    p_ValidDate.Value = account.ValidDate;
                }
                cmd.Parameters.Add(p_ValidDate);
            }

            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("StaticPWD#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_StaticPWD = cmd.CreateParameter();
                p_StaticPWD.ParameterName = "StaticPWD";
                p_StaticPWD.Value = account.PasswordCode;
                cmd.Parameters.Add(p_StaticPWD);
            }
            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("SPWDModifyTime#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_SPWDModifyTime = cmd.CreateParameter();
                p_SPWDModifyTime.ParameterName = "SPWDModifyTime";
                if (account.LastPasswordModify == DateTime.MaxValue
                    || account.LastPasswordModify == DateTime.MinValue)
                {
                    p_SPWDModifyTime.Value = DBNull.Value;
                }
                else
                {
                    p_SPWDModifyTime.Value = account.LastPasswordModify;
                }
                cmd.Parameters.Add(p_SPWDModifyTime);
            }
            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("ProjectTypeID#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_ProjectTypeID = cmd.CreateParameter();
                p_ProjectTypeID.ParameterName = "ProjectTypeID";
                if (account.ProjectTypeID == "")
                    p_ProjectTypeID.Value = DBNull.Value;//ProjectTypeID有外键关联，不能设为空值，只能设为null值
                else
                    p_ProjectTypeID.Value = account.ProjectTypeID;
                cmd.Parameters.Add(p_ProjectTypeID);
            }
            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("ProjectTypeName#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_ProjectTypeName = cmd.CreateParameter();
                p_ProjectTypeName.ParameterName = "ProjectTypeName";
                p_ProjectTypeName.Value = account.ProjectTypeName;
                cmd.Parameters.Add(p_ProjectTypeName);
            }
            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("Position#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_Position = cmd.CreateParameter();
                p_Position.ParameterName = "Position";
                p_Position.Value = account.Position;
                cmd.Parameters.Add(p_Position);
            }
            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("IsManager#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_IsManager = cmd.CreateParameter();
                p_IsManager.ParameterName = "IsManager";
                p_IsManager.Value = account.IsManager;
                cmd.Parameters.Add(p_IsManager);
            }
            if (string.IsNullOrEmpty(paramsStr) || paramsStr.IndexOf("IsDeleted#;", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                DbParameter p_IsDeleted = cmd.CreateParameter();
                p_IsDeleted.ParameterName = "IsDeleted";
                p_IsDeleted.Value = account.IsDeleted;
                cmd.Parameters.Add(p_IsDeleted);
            }
        }
        private static void FillSupplierAccountInfo(DataRow dr, SupplierAccount account)
        {
            if (dr == null)
            {
                throw new ArgumentNullException("dr", "装载供应商帐号信息时，传入的数据行对象不能为空。");
            }
            if (account == null)
            {
                account = new SupplierAccount(dr["AccountID"].ToString());
            }

            account.Name = dr["AccountName"].ToString();
            account.LoginID = dr["LoginID"].ToString();

            account.Mobile = dr["Mobile"].ToString();
            account.Email = dr["Email"].ToString();

            account.SupplierID = dr["SupplierID"].ToString();
            account.Supplier = dr["Supplier"].ToString();
            if (dr["IsDefault"] != null && dr["IsDefault"].ToString() != string.Empty)
            {
                account.IsDefaultAccount = Convert.ToBoolean(dr["IsDefault"].ToString());
            }

            account.PasswordCode = dr["StaticPWD"].ToString();
            if (dr["SPWDModifyTime"] != null && dr["SPWDModifyTime"].ToString() != string.Empty)
            {
                account.LastPasswordModify = Convert.ToDateTime(dr["SPWDModifyTime"].ToString());
            }

            if (dr["ValidDate"] != null && dr["ValidDate"].ToString() != string.Empty)
            {
                account.ValidDate = Convert.ToDateTime(dr["ValidDate"].ToString());
            }
            account.ProjectTypeID = dr["ProjectTypeID"].ToString();
            account.ProjectTypeName = dr["ProjectTypeName"].ToString();
            account.Position = dr["Position"].ToString();
            if (dr["IsManager"] != null && dr["IsManager"].ToString() != string.Empty)
            {
                account.IsManager = Convert.ToBoolean(dr["IsManager"].ToString());
            }
            if (dr["IsDeleted"] != null && dr["IsDeleted"].ToString() != string.Empty)
            {
                account.IsDeleted = Convert.ToBoolean(dr["IsDeleted"].ToString());
            }
        }
        #endregion
    }
}
